﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Common;
using System.Configuration;
using WebApplication.DTO;


namespace WebApplication.DataProvider
{
    public class Provider
    {
        public static DbConnection Connection()
        {
            
            return null;
        }

        public static List<csdlDTO> excuteReaderSQL_DuLieu(DbConnection dbconnect, String sql,int page,ref int soLuong)
        {
            DbCommand dbcommand = dbconnect.CreateCommand();

            dbcommand.CommandText = sql;
            DbDataReader dbresult = dbcommand.ExecuteReader();
            List<csdlDTO> listCSDL = new List<csdlDTO>();
            int recordInPage = 10;
            int start = (page - 1) * recordInPage;
            int end = start + 10;
            int dem = 0;
            int count = 0;

            while (dbresult.Read())
            {
                if (dem < end && dem >=start)
                {
                    csdlDTO cs = new csdlDTO();
                    cs.setId(Convert.ToInt32(dbresult["MaDuLieu"]));
                    cs.setTenDichVu(Convert.ToString(dbresult["TenDichVu"]).Trim().ToUpper());
                    cs.setTenDiaDiem(Convert.ToString(dbresult["TenDiaDiem"]).Trim().ToUpper());
                    cs.setSoNha(Convert.ToString(dbresult["SoNha"]).Trim().ToUpper());
                    cs.setDuong(Convert.ToString(dbresult["TenDuong"]).Trim().ToUpper());
                    cs.setPhuong(Convert.ToString(dbresult["TenPhuong"]).Trim().ToUpper());
                    cs.setQuan(Convert.ToString(dbresult["TenQuanHuyen"]).Trim().ToUpper());
                    cs.setThanhPho(Convert.ToString(dbresult["TenTinhThanh"]).Trim().ToUpper());
                    listCSDL.Add(cs);
                    count++;

                }
                if (count == 10)
                {
                    //break;
                }
                dem++;
            }
            soLuong = dem;
            dbresult.Close();
            return listCSDL;
        }
        public static List<dichvuDTO> excuteReaderSQL_DichVu(DbConnection dbconnect, String sql)
        {
            DbCommand dbcommand = dbconnect.CreateCommand();
            
            dbcommand.CommandText = sql;
            DbDataReader dbresult = dbcommand.ExecuteReader();
            List<dichvuDTO> listDichVu = new List<dichvuDTO>();
            
            while (dbresult.Read())
            {
                dichvuDTO dv = new dichvuDTO();
                dv.MaDichVu = Convert.ToInt32(dbresult["MaDichVu"]);
                dv.TenDichVu = Convert.ToString(dbresult["TenDichVu"]).Trim().ToUpper();

                listDichVu.Add(dv);
            }
            dbresult.Close();
            return listDichVu;
        }

        public static List<diadiemDTO> excuteReaderSQL_DiaDiem(DbConnection dbconnect, String sql)
        {
            DbCommand dbcommand = dbconnect.CreateCommand();

            dbcommand.CommandText = sql;
            DbDataReader dbresult = dbcommand.ExecuteReader();
            List<diadiemDTO> listDiaDiem = new List<diadiemDTO>();

            while (dbresult.Read())
            {
                diadiemDTO dd = new diadiemDTO();
                dd.MaDiaDiem = Convert.ToInt32(dbresult["MaTenDiaDiem"]);
                dd.TenDiaDiem = Convert.ToString(dbresult["TenDiaDiem"]).Trim().ToUpper();

                listDiaDiem.Add(dd);
            }
            dbresult.Close();
            return listDiaDiem;
        }

        public static List<duongDTO> excuteReaderSQL_Duong(DbConnection dbconnect, String sql)
        {
            DbCommand dbcommand = dbconnect.CreateCommand();

            dbcommand.CommandText = sql;
            DbDataReader dbresult = dbcommand.ExecuteReader();
            List<duongDTO> listDuong = new List<duongDTO>();

            while (dbresult.Read())
            {
                duongDTO d = new duongDTO();
                d.MaDuong = Convert.ToInt32(dbresult["MaDuong"]);
                d.TenDuong = Convert.ToString(dbresult["TenDuong"]).Trim().ToUpper();

                listDuong.Add(d);
            }
            dbresult.Close();
            return listDuong;
        }

        public static List<phuongDTO> excuteReaderSQL_Phuong(DbConnection dbconnect, String sql)
        {
            DbCommand dbcommand = dbconnect.CreateCommand();

            dbcommand.CommandText = sql;
            DbDataReader dbresult = dbcommand.ExecuteReader();
            List<phuongDTO> listPhuong = new List<phuongDTO>();

            while (dbresult.Read())
            {
                phuongDTO p = new phuongDTO();
                p.MaPhuong = Convert.ToInt32(dbresult["MaPhuong"]);
                p.TenPhuong = Convert.ToString("phường "+dbresult["TenPhuong"]).Trim().ToUpper();

                listPhuong.Add(p);
            }
            dbresult.Close();
            return listPhuong;
        }

        public static List<quanhuyenDTO> excuteReaderSQL_QuanHuyen(DbConnection dbconnect, String sql)
        {
            DbCommand dbcommand = dbconnect.CreateCommand();

            dbcommand.CommandText = sql;
            DbDataReader dbresult = dbcommand.ExecuteReader();
            List<quanhuyenDTO> listQuanHuyen = new List<quanhuyenDTO>();

            while (dbresult.Read())
            {
                quanhuyenDTO qh = new quanhuyenDTO();
                qh.MaQuanHuyen = Convert.ToInt32(dbresult["MaQuanHuyen"]);
                qh.TenQuanHuyen = Convert.ToString("quận "+dbresult["TenQuanHuyen"]).Trim().ToUpper();

                listQuanHuyen.Add(qh);
            }
            dbresult.Close();
            return listQuanHuyen;
        }

        public static List<thanhphoDTO> excuteReaderSQL_ThanhPho(DbConnection dbconnect, String sql)
        {
            DbCommand dbcommand = dbconnect.CreateCommand();

            dbcommand.CommandText = sql;
            DbDataReader dbresult = dbcommand.ExecuteReader();
            List<thanhphoDTO> listThanhPho = new List<thanhphoDTO>();

            while (dbresult.Read())
            {
                thanhphoDTO tp = new thanhphoDTO();
                tp.MaThanhPho = Convert.ToInt32(dbresult["MaTinhThanh"]);
                tp.TenThanhPho = Convert.ToString(dbresult["TenTinhThanh"]).Trim().ToUpper();

                listThanhPho.Add(tp);
            }
            dbresult.Close();
            return listThanhPho;
        }

        public static List<tukhoaDTO> excuteReaderSQL_TuKhoa(DbConnection dbconnect, String SQL,int indexTable)
        {
            DbCommand dbcommand = dbconnect.CreateCommand();
            List<tukhoaDTO> listTuKhoa = new List<tukhoaDTO>();

            dbcommand.CommandText = SQL;
            DbDataReader dbresult = dbcommand.ExecuteReader();

            while (dbresult.Read())
            {

                tukhoaDTO tk = new tukhoaDTO();
                    
                String strTuKhoa = Convert.ToString(dbresult[0]).Trim();
                tk.Tukhoa = strTuKhoa.Trim();
                tk.MaKey = Convert.ToInt32(dbresult[2]);
                switch (indexTable)
                { 
                    case 3:
                    tk.Key = Convert.ToString("phường "+ dbresult[1]).Trim();
                        break;
                    case 4:
                    tk.Key = Convert.ToString("quận "+ dbresult[1]).Trim();
                        break;
                    case 5:
                    tk.Key = Convert.ToString("thành phố " +dbresult[1]).Trim();
                        break;
                    default :
                        tk.Key = Convert.ToString(dbresult[1]).Trim();
                        break;
                    }
                listTuKhoa.Add(tk);
            }
                dbresult.Close();
            return listTuKhoa;
        }

    }
}